<?php
class ZranknvsController extends AppController {

	var $name = 'Zranknvs';
	var $helpers = array('Html', 'Form','Js');
    var $uses=array('Zranknv','Donvi','Thuocdv','Z1dvxephang');
    /**
     * index:Xếp hạng các nhân viên dựa vào đơn vị được chọn
     * */
	function index() {
	      $kq=array(); $nhanviens=array();
        if(!empty($this->data)){
            $ngaybd = date('Y-m-d',strtotime($this->data['Zranknv']['ngaybd']));
            $ngaykt = date('Y-m-d',strtotime($this->data['Zranknv']['ngaykt']));
    	   $kq=$this->Zranknv->query("
                 select donvis.tendv,nv.manv, nv.tennv, if(sum(diemquydoi) is null,0,sum(diemquydoi)) as diemqd, 
                                    if(sum(diemxh) is null,0,sum(diemxh)) as xh
                from donvis,zranknvs as kq right join nhanviens nv on kq.nhanvien_id=nv.id
                
                where ngay between '".$ngaybd."' 
                                and '".$ngaykt."'
                    and nv.donvi_id='".$this->data['Zranknv']['donvi_id']."'
                    and donvis.id=nv.donvi_id
                group by donvis.tendv,nv.manv, nv.tennv
                order by 5 desc,1,2
           ");
           //loc ds sv dc xep hang (do co ban trong ngay dc chon)
           $this->Zranknv->recursive=-1;
           $nvs=$this->Zranknv->find('list',array(
                    'fields'=>array('nhanvien_id','nhanvien_id'),
                    'conditions'=>array('donvi_id'=>$this->data['Zranknv']['donvi_id'],
                                'ngay between ? and ?'=>array($ngaybd,$ngaykt)))); 
         //loc nhung nhanvien chua ban duoc j ra
           $this->Nhanvien->recursive=0;
           $nhanviens=$this->Nhanvien->find('all',array(
                    'conditions'=>array(
                            'NOT'=>array('Nhanvien.id'=>$nvs),
                            'AND'=>array('donvi_id'=>$this->data['Zranknv']['donvi_id'])),
                    'fields'=>array('Donvi.tendv','Nhanvien.manv','Nhanvien.tennv')));
          // print_r($nhanviens);
             
        }
        // print_r($kq);
        $this->Donvi->recursive=-1;
        $donvis=$this->Donvi->find('list',array('fields'=>array('Donvi.id','tendv')));
        $this->set(compact('kq','donvis','nhanviens'));
	
	}
    /**
     * index: x?p h?ng c�c nh�n vi�n NHI?U 1 �ON V? d?a v�o d? li?u: ngaybd, ngaykt,donvi
     * Tru?ng h?p nh�n vi�n kh�ng b�n (k co du lieu) trong dieu kien da chon 
     * thi v?n hi?n d? li?u (l?y d? li?u t? c�c nh�n vi�n c�n l?i trong C�C don v? d�)
     * */
	function indexnvtts() {
        $kq=array(); $nhanviens=array(); $donvis=array();$previous=array();
        if(!empty($this->data)){
            if(!empty($this->data['Zranknv']['donvi_con'])){
                $ngaybd = date('Y-m-d',strtotime($this->data['Zranknv']['ngaybd']));
                $ngaykt = date('Y-m-d',strtotime($this->data['Zranknv']['ngaykt']));
    	       //$sodv=count($this->data['Zranknv']['donvi_con']);
            
            //GHEP CHUOI de thuc hien lenh IN
                $giatri='(';
                $i=0;
                foreach ($this->data['Zranknv']['donvi_con'] as $dvs){
                    if($i==0){
                        $giatri.=$dvs;
                        
                    }
                    else    
                        $giatri.=','.$dvs;
                    
                    $previous[$i]=$dvs;
                    $i++;
                    
                }
                $giatri.=')';
            
            //print_r($giatri);
                $kq=$this->Zranknv->query("
                    select tendv,manv, tennv, sum(diemquydoi) as diemqd, sum(diemxh) as xh
                    from zranknvs as kq 
                    where ngay between '".$ngaybd."' 
                                and '".$ngaykt."'
                        and donvi_id in ".$giatri."
                    group by tendv, manv, tennv
                    order by 5 desc,1,2
                "); 
           
            //loc ds sv dc xep hang (do co ban trong ngay dc chon)
                $this->Zranknv->recursive=-1;
                $nvs=$this->Zranknv->find('list',array(
                    'fields'=>array('nhanvien_id','nhanvien_id'),
                    'conditions'=>array('donvi_id'=>$this->data['Zranknv']['donvi_con'],
                                'ngay between ? and ?'=>array($ngaybd,$ngaykt)))); 
         //loc nhung nhanvien chua ban duoc j ra
                $this->Nhanvien->recursive=0;
                $nhanviens=$this->Nhanvien->find('all',array(
                    'conditions'=>array(
                            'NOT'=>array('Nhanvien.id'=>$nvs),
                            'AND'=>array('donvi_id'=>$this->data['Zranknv']['donvi_con'])),
                    'fields'=>array('Donvi.tendv','Nhanvien.manv','Nhanvien.tennv')));
                
           //print_r($nhanviens);
           
                }else{
                    $this->Session->setFlash(__('Bạn chưa chọn đơn vị nào', true));                 
                }    
         }
        //print_r($kq);
        //chi loc ra nhung don vi c� "con"
            $this->Thuocdv->recursive=-1;
            $dvcha=$this->Thuocdv->find('list',array('fields'=>array('donvi_id','donvi_id')));
        //lay ten donvi, va id cua no
            $this->Donvi->recursive=-1;
            $donvis=$this->Donvi->find('list',array(
                    'fields'=>array('Donvi.id','tendv'),
                    'conditions'=>array('id'=>$dvcha)));
        
        $donvidus=$this->Donvi->find('list',array(
                    'fields'=>array('Donvi.id','tendv'),'conditions'=>array('Donvi.id'=>$previous)));
        
        $this->set(compact('kq','donvis','nhanviens','donvidus','previous'));
	
	}
    
    
     /**
      * Dung de xep hang các đơn vị
     * */
	function indexdv() {
        $kq=array();  $donvis=array();$previous=array();$nhanviens=array();
        if(!empty($this->data)){
            if(!empty($this->data['Zranknv']['donvi_con'])){
                $ngaybd = date('Y-m-d',strtotime($this->data['Zranknv']['ngaybd']));
                $ngaykt = date('Y-m-d',strtotime($this->data['Zranknv']['ngaykt']));
    	       //$sodv=count($this->data['Zranknv']['donvi_con']);
            
            //GHEP CHUOI de thuc hien lenh IN
                $giatri='(';
                $i=0;
                foreach ($this->data['Zranknv']['donvi_con'] as $dvs){
                    if($i==0){
                        $giatri.=$dvs;
                        
                    }
                    else    
                        $giatri.=','.$dvs;
                    
                    $previous[$i]=$dvs;
                    $i++;
                    
                }
                $giatri.=')';
            
            //print_r($giatri);
                $kq=$this->Zranknv->query("
                    select tendv, sum(diem*tyle) as diemqd, sum(quydoi) as xh
                    from z1dvxephangs as kq 
                    where ngay between '".$ngaybd."' 
                                and '".$ngaykt."'
                        and donvi_id in ".$giatri."
                    group by tendv
                    order by 3 desc,1,2
                "); 
           
            //loc ds sv dc xep hang (do co ban trong ngay dc chon)
                $this->Zranknv->recursive=-1;
                $nvs=$this->Z1dvxephang->find('list',array(
                    'fields'=>array('donvi_id','donvi_id'),
                    'conditions'=>array('donvi_id'=>$this->data['Zranknv']['donvi_con'],
                                'ngay between ? and ?'=>array($ngaybd,$ngaykt)))); 
         //loc nhung nhanvien chua ban duoc j ra
                $this->Donvi->recursive=-1;
                $nhanviens=$this->Donvi->find('list',array(
                    'conditions'=>array(
                            'NOT'=>array('Donvi.id'=>$nvs),
                            'AND'=>array('Donvi.id'=>$this->data['Zranknv']['donvi_con'])),
                    'fields'=>array('Donvi.id','Donvi.tendv')));
                
           //print_r($nhanviens);
           
                }else{
                    $this->Session->setFlash(__('Bạn chưa chọn đơn vị nào', true));                 
                }    
         }
        //print_r($kq);
        //chi loc ra nhung don vi c� "con"
            $this->Thuocdv->recursive=-1;
            $dvcha=$this->Thuocdv->find('list',array('fields'=>array('donvi_id','donvi_id')));
        //lay ten donvi, va id cua no
            $this->Donvi->recursive=-1;
            $donvis=$this->Donvi->find('list',array(
                    'fields'=>array('Donvi.id','tendv'),
                    'conditions'=>array('id'=>$dvcha)));
        
        $donvidus=$this->Donvi->find('list',array(
                    'fields'=>array('Donvi.id','tendv'),'conditions'=>array('Donvi.id'=>$previous)));
        
        $this->set(compact('kq','donvis','nhanviens','donvidus','previous'));
	
	}
   
    function selectdv(){
		$this->layout = null;
	//	$this->layout = 'blank';  //code nay de hien ket qua ra dang bang de kiem thu
		if(!empty($this->data['Donvi']['id'])){
			$tinh_id = (int)$this->data['Donvi']['id'];
            
            $this->Thuocdv->recursive=-1;
			$dv_ids= $this->Thuocdv->find('list', array(
                    'conditions'=>array('donvi_id'=>$tinh_id),
                    'fields'=>array('donvi','donvi')));
            
            $this->Donvi->recursive=-1;
            $options=$this->Donvi->find('list', array(
                    'conditions'=>array('id'=>$dv_ids),
                    'fields'=>array('id','tendv')));
			
            $this->set('options',$options);
		}
	}
	



}
?>